package com.java.oop.ims.sql;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Test {

    public static void main(String[] args) {
        Test test = new Test();

        //test.bakInhouse();

        //test.bakInhouseRecord();
        //test.bakInhouseLog();
        //test.createInhouse();
        //test.createInhouseRecord();
        //test.createInhouseLog();
        //test.createRegionChangeLog();
        //test.createRegionRecord();


        Map<String, List<String>> billMap = new LinkedHashMap<>();
        List<String> l1 = new ArrayList<>();
//        l1.add("QDBCJH202001070001596");
//        l1.add("QDBCJH202003140000285");
//        l1.add("QDBCJH202003270000272");
//        l1.add("QDBCJH202003280000085");
//        l1.add("QDBCJH202003140000286");
//        l1.add("QDBCJH202002180000140");
//        l1.add("QDBCJH202003170000241");
//        l1.add("QDBCJH20191225000248");
//        l1.add("QDBCJH202003140000287");
//        l1.add("QDBCJH202004140000290");
//        l1.add("QDBCJH202004300000220");
//        l1.add("QDBCJH202003090000188");
        //billMap.put("JDX", l1);

//        List<String> l2 = new ArrayList<>();
//
//        l2.add("QDBCJH20191231001790");
//        l2.add("QDBCJH202001140000770");
//        l2.add("QDBCJH202003110000153");
//        l2.add("QDBCJH202003130000158");
//        l2.add("QDBCJH202003270000300");
//        l2.add("QDBCJH202003270000299");
//        l2.add("QDBCJH202001140000763");
//        l2.add("QDBCJH202005060000212");
//        l2.add("QDBCJH202003130000159");
//        l2.add("QDBCJH202001140000767");
//        l2.add("QDBCJH202001060000346");
//        l2.add("QDBCJH202001140000759");
//        l2.add("QDBCJH202003280000033");
//        l2.add("QDBCJH202001100000379");
//        l2.add("QDBCJH202003200000098");
//        l2.add("QDBCJH202004150000154");
//        l2.add("QDBCJH20200102005021");
//        l2.add("QDBCJH202003280000144");
//        l2.add("QDBCJH202004280000154");
//        l2.add("QDBCJH202003190000240");
//        l2.add("QDBCJH202004290000215");
//        l2.add("QDBCJH202004150000149");
//        l2.add("QDBCJH202003160000274");
//        l2.add("QDBCJH202004080000017");
//        l2.add("QDBCJH202004220000109");
//        l2.add("QDBCJH202003280000031");
//        l2.add("QDBCJH202004290000221");
//        l2.add("QDBCJH202003270000301");
//        l2.add("QDBCJH202003140000087");
//        l2.add("QDBCJH202004070000250");
//        l2.add("QDBCJH202004290000218");
//        l2.add("QDBCJH202004150000150");
//        l2.add("QDBCJH202004020000332");
//        billMap.put("MCX", l2);
//
//        List<String> l3 = new ArrayList<>();
//        l3.add("QDBCJH202001080000499");
//        l3.add("QDBCJH202001110000812");
//        l3.add("QDBCJH202003040000108");
////        billMap.put("SFX", l3);
//        List<String> l4 = new ArrayList<>();
//        l4.add("QDBCJH20190927000149");
//        l4.add("QDBCJH20191230003565");
//        l4.add("QDBCJH202001080001259");
//        l4.add("QDBCJH202004210000290");
//        l4.add("QDBCJH202004210000293");
//        l4.add("QLSCJH20190730000005");
//        l4.add("QDBCJH202001060001144");
//        l4.add("QDBCJH202003230000097");
//        l4.add("QDBCJH20190824000122");
//        l4.add("QDBCJH202004210000296");
//        l4.add("QDBCJH202004210000289");
//        l4.add("QDBCJH202004210000278");
//        l4.add("QDBCJH202003120000410");
//        l4.add("QDBCJH202003170000229");
//        l4.add("QDBCJH202004230000144");
//        l4.add("QDBCJH202004210000294");
//        l4.add("QDBCJH202003120000398");
//        l4.add("QDBCJH202001080001391");
//        billMap.put("XFXX", l4);

//
//        List<String> l5 = new ArrayList<>();
//        l5.add("QDBCJH202004180000078");
//        l5.add("QDBCJH202005040000051");
//        l5.add("QDBCJH202004240000223");
//        l5.add("QDBCJH202004270000229");
//        l5.add("QDBCJH202005040000144");
//        l5.add("QDBCJH202004300000126");
//        l5.add("QDBCJH202005040000167");
//        l5.add("QDBCJH202005030000072");
//        l5.add("QDBCJH202005040000168");
//        l5.add("QDBCJH202005040000166");
//        l5.add("QDBCJH202005050000033");
//
//        l5.add("QDBCJH202005050000033");
//        l5.add("QDBCJH202005050000030");
//        l5.add("QDBCJH202005050000211");
//        billMap.put("TMX", l5);

        //test.updateOnwayRecordChannel(billMap);
        test.gaobaiheSelectSql();




    }


    private void gaobaiheSelectSql() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 2; i <= 2; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "\n" +
                        "SELECT CONCAT('INSERT INTO `snt_ims_sub_" + df.format(i) + "`.`inventory_inhouse_record_" + tf.format(j) + "`(`id`, `inhouse_id`, `product_guid`, `product_bar_code`, `product_name`, `product_type`, `warehouse_id`, `warehouse_name`, `warehouse_type`, `warehouse_region_code`, `warehouse_region_name`, `stock_type`, `supply_code`, `supply_name`, `opt_num`, `record_status`, `storage_type`, `channel_code`, `bill_no`, `relc_num`, `diff_num`, `bill_row`, `bill_type`, `channel_name`, `lock_time`, `release_time`, `gmt_modified`, `ext_att`, `row_version`, `row_status`, `biz_type`, `gmt_create`, `out_warehouse_date`, `remark`, `creator`, `operator`, \n" +
                        "`region_flag`, `region_channel_code`, `region_channel_name`, `source_system`, `pre_overdue`) \n" +
                        "VALUES (',id,',',\n" +
                        "IFNULL(inhouse_id,'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',product_guid,'\\''),'NULL'),',',IFNULL(CONCAT('\\'',product_bar_code,'\\''),'NULL'),',',IFNULL(CONCAT('\\'',product_name,'\\''),'NULL'),',',\n" +
                        "IFNULL(product_type,'NULL'),',',\n" +
                        "IFNULL(warehouse_id,'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',warehouse_name,'\\''),'NULL'),',',\n" +
                        "IFNULL(warehouse_type,'warehouse_type'),',',\n" +
                        "IFNULL(CONCAT('\\'',warehouse_region_code,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',warehouse_region_name,'\\''),'NULL'),',',\n" +
                        "IFNULL(stock_type,'NULL'),',',IFNULL(CONCAT('\\'',supply_code,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',supply_name,'\\''),'NULL'),',',IFNULL(opt_num,'NULL'),',',IFNULL(record_status,'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',storage_type,'\\''),'NULL'),',',IFNULL(CONCAT('\\'',CHANNEL_CODE,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',bill_no,'\\''),'NULL'),',',IFNULL(relc_num,'NULL'),',',\n" +
                        "IFNULL(diff_num,'NULL'),',',IFNULL(CONCAT('\\'',bill_row,'\\''),'NULL'),',',IFNULL(CONCAT('\\'',bill_type,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',channel_name,'\\''),'NULL'),','," +
                        "IFNULL(CONCAT('\\'',lock_time,'\\''),'NULL'),','," +
                        "IFNULL(CONCAT('\\'',release_time,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',gmt_modified,'\\''),'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',ext_att,'\\''),'NULL'),',',\n" +
                        "IFNULL(row_version,'NULL'),',',\n" +
                        "IFNULL(row_status,'NULL'),',',\n" +
                        "IFNULL(biz_type,'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',gmt_create,'\\''),'NULL'),',',\n" +
                        "IFNULL(out_warehouse_date,'NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',remark,'\\''),'NULL'),',',\n" +
                        "IFNULL(creator,'NULL'),',',\n" +
                        "IFNULL(operator,'NULL'),',',\n" +
                        "IFNULL(1,'NULL'),',',\n" +
                        "IFNULL('\\'SFX\\'','NULL'),',',\n" +
                        "IFNULL('\\'三方系\\'','NULL'),',',\n" +
                        "IFNULL(CONCAT('\\'',source_system,'\\''),'NULL'),',',\n" +
                        "IFNULL(pre_overdue,'NULL'), ')') from " + "snt_ims_sub" + "_" + df.format(i);
                sql += ".inventory_inhouse_record" + "_" + tf.format(j) + " a where warehouse_id =  1001311 and stock_type in(2,3) AND record_status = 0";
                if (j == 128 && i == 6) {
                    sql += " ;";

                } else {
                    sql += " UNION ALL ";
                }
                System.out.println(sql);
            }
        }


    }


    /**
     * 备份inhouse表语句
     */
    private void bakInhouse() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "alter table snt_ims_sub_" + df.format(i) + ".inventory_inhouse_" + tf.format(j) + " rename to snt_ims_sub_" + df.format(i) + ".inventory_inhouse_" + tf.format(j) + "_bak;\n ";
                System.out.println(sql);
            }
        }
    }

    /**
     * 备份inventory_inhouse_record表语句
     */
    private void bakInhouseRecord() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "alter table snt_ims_sub_" + df.format(i) + ".inventory_inhouse_record_" + tf.format(j) + " rename to snt_ims_sub_" + df.format(i) + ".inventory_inhouse_record_" + tf.format(j) + "_bak;\n ";
                System.out.println(sql);
            }
        }
    }

    /**
     * 备份inventory_inhouse_log表语句
     */
    private void bakInhouseLog() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "alter table snt_ims_sub_" + df.format(i) + ".inventory_inhouse_log_" + tf.format(j) + " rename to snt_ims_sub_" + df.format(i) + ".inventory_inhouse_log_" + tf.format(j) + "_bak;\n ";
                System.out.println(sql);
            }
        }
    }

    private void createInhouse() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "\n" +
                        "CREATE TABLE snt_ims_sub_" + df.format(i) + ".`inventory_inhouse_" + tf.format(j) + "` (\n" +
                        "`id`  bigint(20) NOT NULL COMMENT '主键' ,\n" +
                        "`product_guid`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '货品guid' ,\n" +
                        "`product_bar_code`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货品barcode' ,\n" +
                        "`product_name`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货品名' ,\n" +
                        "`product_type`  int(4) NULL DEFAULT NULL COMMENT '货品类型' ,\n" +
                        "`warehouse_id`  int(20) NOT NULL COMMENT '仓库id' ,\n" +
                        "`warehouse_name`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库名' ,\n" +
                        "`warehouse_type`  int(2) NULL DEFAULT NULL COMMENT '目的仓库类型10普通仓库20cp仓库' ,\n" +
                        "`warehouse_region_code`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库区域id' ,\n" +
                        "`warehouse_region_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库区域名' ,\n" +
                        "`supply_code`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商编码' ,\n" +
                        "`supply_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商名' ,\n" +
                        "`total_num`  bigint(20) NOT NULL DEFAULT 0 COMMENT '账面总数' ,\n" +
                        "`lock_num`  bigint(20) NULL DEFAULT 0 COMMENT '锁定数' ,\n" +
                        "`reserve_num`  bigint(20) NULL DEFAULT 0 COMMENT '预留数' ,\n" +
                        "`available_num`  bigint(20) NOT NULL DEFAULT 0 COMMENT '可用数' ,\n" +
                        "`storage_type`  varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '库区' ,\n" +
                        "`channel_code`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '渠道id' ,\n" +
                        "`channel_name`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '渠道名' ,\n" +
                        "`gmt_modified`  datetime NULL DEFAULT NULL COMMENT '修改时间' ,\n" +
                        "`ext_att`  varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '其他' ,\n" +
                        "`row_version`  int(11) NULL DEFAULT NULL COMMENT '行版本' ,\n" +
                        "`row_status`  int(11) NOT NULL DEFAULT 0 COMMENT '行状态0有效-1删除' ,\n" +
                        "`biz_type`  int(11) NULL DEFAULT NULL COMMENT '套账' ,\n" +
                        "`gmt_create`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,\n" +
                        "`remark`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注' ,\n" +
                        "`creator`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人' ,\n" +
                        "`operator`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人' ,\n" +
                        "`inventory_status`  int(3) NULL DEFAULT 0 COMMENT '库存状态 预留' ,\n" +
                        "`assemble_status`  int(2) NULL DEFAULT 0 COMMENT '产品组合状态[0单品|1组合|2礼包]' ,\n" +
                        "PRIMARY KEY (`id`),\n" +
                        "INDEX `inventory_inhouse_product_guid_idx` (`product_guid`) USING BTREE \n" +
                        ")\n" +
                        "ENGINE=InnoDB\n" +
                        "DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci\n" +
                        "COMMENT='在库库存'\n" +
                        "ROW_FORMAT=COMPACT\n" +
                        ";\n";
                System.out.println(sql);
            }
        }

    }

    private void createInhouseRecord() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "CREATE TABLE snt_ims_sub_" + df.format(i) + ".`inventory_inhouse_record_" + tf.format(j) + "` (\n" +
                        "`id`  bigint(20) NOT NULL COMMENT '主键' ,\n" +
                        "`inhouse_id`  bigint(20) NOT NULL COMMENT '在库库存id' ,\n" +
                        "`product_guid`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '货品guid' ,\n" +
                        "`product_bar_code`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货品barcode' ,\n" +
                        "`product_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货品名' ,\n" +
                        "`product_type`  int(4) NULL DEFAULT 1 COMMENT '货品类型' ,\n" +
                        "`warehouse_id`  int(11) NOT NULL DEFAULT 0 COMMENT '仓库id' ,\n" +
                        "`warehouse_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库名' ,\n" +
                        "`warehouse_type`  int(11) NULL DEFAULT 7 COMMENT '仓库类型:1-配送中心DC2-区域配送小DC3-RDC仓库4-线下体验店5-生产仓库 6投食店 7自有仓库 8仓配仓库 9自营唯品会\\n\\n10自营猫超 11自营苏宁小店 12自营苏宁\\n\\n13零售通仓库 14自营京东 15电商仓 0其他' ,\n" +
                        "`warehouse_region_code`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库区域id' ,\n" +
                        "`warehouse_region_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库区域名' ,\n" +
                        "`stock_type`  int(2) NOT NULL DEFAULT 1 COMMENT '2锁定库存 3预留库存' ,\n" +
                        "`supply_code`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商编码' ,\n" +
                        "`supply_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商名' ,\n" +
                        "`opt_num`  bigint(20) NOT NULL DEFAULT 0 COMMENT '锁定数' ,\n" +
                        "`record_status`  int(4) NULL DEFAULT 0 COMMENT '锁定单状态0锁定 1已经归还（释放） 2扣减' ,\n" +
                        "`storage_type`  varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '库区' ,\n" +
                        "`channel_code`  varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '渠道id' ,\n" +
                        "`bill_no`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,\n" +
                        "`relc_num`  bigint(20) NULL DEFAULT 0 COMMENT '释放/减少库存数目' ,\n" +
                        "`diff_num`  bigint(20) NULL DEFAULT 0 COMMENT '差异数量 锁定/预留数量-释放/扣减数量' ,\n" +
                        "`bill_row`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,\n" +
                        "`bill_type`  varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单据类型' ,\n" +
                        "`channel_name`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '渠道名' ,\n" +
                        "`lock_time`  datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '锁定（预留）时间' ,\n" +
                        "`release_time`  datetime NULL DEFAULT NULL COMMENT '释放/扣减时间' ,\n" +
                        "`gmt_modified`  datetime NULL DEFAULT NULL COMMENT '修改时间' ,\n" +
                        "`ext_att`  varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '其他' ,\n" +
                        "`row_version`  int(11) NULL DEFAULT NULL COMMENT '行版本' ,\n" +
                        "`row_status`  int(11) NULL DEFAULT NULL COMMENT '行状态' ,\n" +
                        "`biz_type`  int(11) NULL DEFAULT NULL COMMENT '套账' ,\n" +
                        "`gmt_create`  datetime NULL DEFAULT NULL COMMENT '创建时间' ,\n" +
                        "`out_warehouse_date`  datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'wms出库时间' ,\n" +
                        "`remark`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注' ,\n" +
                        "`creator`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人' ,\n" +
                        "`operator`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人' ,\n" +
                        "`region_flag` int(4) DEFAULT NULL COMMENT '同步操作大区 0-否 1-是',\n" +
                        "  `region_channel_code` varchar(100) DEFAULT NULL COMMENT '大区渠道code',\n" +
                        "  `region_channel_name` varchar(100) DEFAULT NULL COMMENT '大区渠道',\n" +
                        "`source_system`  varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '来源系统' ,\n" +
                        "`pre_overdue`  int(3) NULL DEFAULT 0 COMMENT '临期等级 1,2,3,4  默认0 非临期商品锁定' ,\n" +
                        "PRIMARY KEY (`id`),\n" +
                        "INDEX `inventory_inhouse_record_bill_no_idx` (`bill_no`) USING BTREE ,\n" +
                        "INDEX `lock_time` (`lock_time`) USING BTREE ,\n" +
                        "INDEX `inventory_inhouse_record_record_status_idx` (`record_status`) USING BTREE \n" +
                        ")\n" +
                        "ENGINE=InnoDB\n" +
                        "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci\n" +
                        "COMMENT='在库库存锁定和预留记录'\n" +
                        "ROW_FORMAT=COMPACT\n" +
                        ";\n";
                System.out.println(sql);
            }
        }

    }

    private void createInhouseLog() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "CREATE TABLE snt_ims_sub_" + df.format(i) + ".`inventory_inhouse_log_" + tf.format(j) + "` (\n" +
                        "  `id` bigint(20) NOT NULL COMMENT '主键',\n" +
                        "  `inhouse_id` bigint(20) NOT NULL COMMENT '在库id',\n" +
                        "  `warehouse_region_name` varchar(100) DEFAULT NULL COMMENT '仓库区域名',\n" +
                        "  `warehouse_region_code` varchar(20) DEFAULT NULL COMMENT '仓库区域id',\n" +
                        "  `warehouse_type` int(11) DEFAULT NULL COMMENT '仓库类型',\n" +
                        "  `warehouse_name` varchar(100) DEFAULT NULL COMMENT '仓库名',\n" +
                        "  `warehouse_id` int(11) NOT NULL COMMENT '仓库id',\n" +
                        "  `product_type` int(11) DEFAULT NULL COMMENT '货品类型',\n" +
                        "  `product_name` varchar(50) DEFAULT NULL COMMENT '货品名',\n" +
                        "  `product_bar_code` varchar(50) DEFAULT NULL COMMENT '货品barcode',\n" +
                        "  `product_guid` varchar(50) NOT NULL COMMENT '货品guid',\n" +
                        "  `opt_num` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作数量',\n" +
                        "  `bill_no` varchar(50) NOT NULL COMMENT '单据号',\n" +
                        "  `bill_row` varchar(128) DEFAULT NULL COMMENT '单据行号',\n" +
                        "  `bill_type` varchar(20) NOT NULL COMMENT '单据类型',\n" +
                        "  `source_system` varchar(20) NOT NULL COMMENT '来源系统',\n" +
                        "  `change_type` int(2) NOT NULL DEFAULT '1' COMMENT '变动类型 1增加 2减少',\n" +
                        "  `stock_type` int(2) NOT NULL DEFAULT '1' COMMENT '库存类型 1账面总库存 2锁定库存 3预留库存 可用库存',\n" +
                        "  `storage_type` varchar(10) NOT NULL COMMENT '库区',\n" +
                        "  `CHANNEL_CODE` varchar(20) DEFAULT NULL COMMENT '渠道id',\n" +
                        "  `channel_name` varchar(100) DEFAULT NULL COMMENT '渠道名',\n" +
                        "  `ims_after_lock_num` bigint(21) DEFAULT NULL COMMENT 'ims调整后锁定数',\n" +
                        "  `ims_before_lock_num` bigint(21) DEFAULT NULL COMMENT 'ims调整前锁定数',\n" +
                        "  `ims_after_reserve_num` bigint(21) DEFAULT NULL COMMENT 'ims调整后预留数',\n" +
                        "  `ims_before_reserve_num` bigint(21) DEFAULT NULL COMMENT 'ims调整前预留数',\n" +
                        "  `ims_after_available_num` bigint(21) DEFAULT NULL COMMENT 'ims调整后可用数',\n" +
                        "  `ims_before_available_num` bigint(21) DEFAULT NULL COMMENT 'ims调整前可用数',\n" +
                        "  `ims_after_total_num` bigint(21) DEFAULT NULL COMMENT 'ims调整后总数',\n" +
                        "  `ims_before_total_num` bigint(21) DEFAULT NULL COMMENT 'ims调整前总数',\n" +
                        "  `creator` varchar(20) DEFAULT NULL COMMENT '创建人',\n" +
                        "  `operator` varchar(20) DEFAULT NULL COMMENT '操作人',\n" +
                        "  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',\n" +
                        "  `ext_att` varchar(1000) DEFAULT NULL COMMENT '其他',\n" +
                        "  `row_version` int(11) DEFAULT NULL COMMENT '行版本',\n" +
                        "  `row_status` int(11) DEFAULT NULL COMMENT '行状态',\n" +
                        "  `biz_type` int(11) DEFAULT NULL COMMENT '套账',\n" +
                        "  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                        "  `out_warehouse_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'wms出库时间',\n" +
                        "  `remark` varchar(1000) DEFAULT NULL COMMENT '备注',\n" +
                        "  `assemble_status` int(4) DEFAULT '0' COMMENT '产品组合状态[0单品|1组合|2礼包]',\n" +
                        "  PRIMARY KEY (`id`),\n" +
                        "  KEY `idx_bill_no` (`bill_no`) USING BTREE,\n" +
                        "  KEY `idx_product_guid` (`product_guid`) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='在库库存日志';";
                System.out.println(sql);
            }
        }

    }


    private void createRegionChangeLog() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "CREATE TABLE snt_ims_sub_" + df.format(i) + ".`inventory_region_change_log_" + tf.format(j) + "` (\n" +
                        "  `id` bigint(20) NOT NULL COMMENT '主键',\n" +
                        "  `inventory_region_id` bigint(20) NOT NULL COMMENT '大区库存id',\n" +
                        "  `product_guid` varchar(50) DEFAULT NULL COMMENT '货品guid',\n" +
                        "  `product_bar_code` varchar(50) DEFAULT NULL COMMENT '货品barcode',\n" +
                        "  `product_name` varchar(100) DEFAULT NULL COMMENT '货品名',\n" +
                        "  `region_warehouse_id` int(11) NOT NULL DEFAULT '0' COMMENT '大区id',\n" +
                        "  `region_warehouse_name` varchar(50) DEFAULT NULL COMMENT '大区名称',\n" +
                        "  `storage_type` varchar(10) DEFAULT NULL COMMENT '库区',\n" +
                        "  `channel_code` varchar(20) DEFAULT NULL COMMENT '渠道CODE',\n" +
                        "  `before_total_num` bigint(20) DEFAULT '0' COMMENT '账面总数',\n" +
                        "  `before_lock_num` bigint(20) DEFAULT '0' COMMENT '锁定数',\n" +
                        "  `before_reserve_num` bigint(20) DEFAULT '0' COMMENT '预留数',\n" +
                        "  `before_available_num` bigint(20) DEFAULT '0' COMMENT '可用数',\n" +
                        "  `param_total_num` bigint(20) DEFAULT '0' COMMENT '账面总数',\n" +
                        "  `param_lock_num` bigint(20) DEFAULT '0' COMMENT '锁定数',\n" +
                        "  `param_reserve_num` bigint(20) DEFAULT '0' COMMENT '预留数',\n" +
                        "  `param_available_num` bigint(20) DEFAULT '0' COMMENT '可用数',\n" +
                        "  `after_total_num` bigint(20) DEFAULT '0' COMMENT '账面总数',\n" +
                        "  `after_lock_num` bigint(20) DEFAULT '0' COMMENT '锁定数',\n" +
                        "  `after_reserve_num` bigint(20) DEFAULT '0' COMMENT '预留数',\n" +
                        "  `after_available_num` bigint(20) DEFAULT '0' COMMENT '可用数',\n" +
                        "  `bill_no` varchar(3000) DEFAULT NULL,\n" +
                        "  `bill_row` varchar(3000) DEFAULT NULL,\n" +
                        "  `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',\n" +
                        "  `ext_att` varchar(3000) DEFAULT NULL COMMENT '其他',\n" +
                        "  `row_version` int(11) DEFAULT NULL COMMENT '行版本',\n" +
                        "  `row_status` int(11) DEFAULT NULL COMMENT '行状态',\n" +
                        "  `biz_type` int(11) DEFAULT NULL COMMENT '套账',\n" +
                        "  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',\n" +
                        "  `creator` varchar(50) DEFAULT NULL COMMENT '创建人',\n" +
                        "  PRIMARY KEY (`id`),\n" +
                        "  KEY `inventory_region_record_bill_no_idx` (`bill_no`(191)) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='大区库存操作日志';\n" +
                        "\n" +
                        "\n";

                System.out.println(sql);
            }
        }

    }

    private void createRegionRecord() {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (int i = 1; i <= 6; i++) {
            for (int j = 1; j <= 128; j++) {
                String sql = "CREATE TABLE snt_ims_sub_" + df.format(i) + ".`inventory_region_record_" + tf.format(j) + "` (\n" +
                        "  `id` bigint(20) NOT NULL COMMENT '主键',\n" +
                        "  `inventory_region_id` bigint(20) NOT NULL COMMENT '大区库存id',\n" +
                        "  `product_guid` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '货品guid',\n" +
                        "  `product_bar_code` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '货品barcode',\n" +
                        "  `product_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '货品名',\n" +
                        "  `product_type` int(4) DEFAULT '1' COMMENT '货品类型',\n" +
                        "  `region_warehouse_id` int(11) NOT NULL DEFAULT '0' COMMENT '大区id',\n" +
                        "  `region_warehouse_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '大区名称',\n" +
                        "  `stock_type` int(2) NOT NULL DEFAULT '1' COMMENT '2锁定库存 3预留库存',\n" +
                        "  `opt_num` bigint(20) NOT NULL DEFAULT '0' COMMENT '锁定数',\n" +
                        "  `record_status` int(4) DEFAULT '0' COMMENT '锁定单状态0锁定 1已经归还（释放） 2扣减',\n" +
                        "  `storage_type` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '库区',\n" +
                        "  `channel_code` varchar(40) CHARACTER SET utf8mb4 NOT NULL COMMENT '渠道CODE',\n" +
                        "  `channel_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '渠道名',\n" +
                        "  `bill_no` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,\n" +
                        "  `relc_num` bigint(20) DEFAULT '0' COMMENT '释放/扣减库存数目',\n" +
                        "  `diff_num` bigint(20) DEFAULT '0' COMMENT '差异数量 锁定/预留数量-释放/扣减数量',\n" +
                        "  `bill_row` varchar(64) DEFAULT NULL,\n" +
                        "  `bill_type` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '单据类型 1000:仓同步 2000:仓未同步',\n" +
                        "  `lock_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '锁定（预留）时间',\n" +
                        "  `release_time` datetime DEFAULT NULL COMMENT '释放/扣减时间',\n" +
                        "  `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',\n" +
                        "  `ext_att` varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '其他',\n" +
                        "  `row_version` int(11) DEFAULT NULL COMMENT '行版本',\n" +
                        "  `row_status` int(11) DEFAULT NULL COMMENT '行状态',\n" +
                        "  `biz_type` int(11) DEFAULT NULL COMMENT '套账',\n" +
                        "  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',\n" +
                        "  `out_warehouse_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'wms出库时间',\n" +
                        "  `remark` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '备注',\n" +
                        "  `creator` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '创建人',\n" +
                        "  `operator` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '更新人',\n" +
                        "  `source_system` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '来源系统',\n" +
                        "  PRIMARY KEY (`id`),\n" +
                        "  UNIQUE KEY `unique_key` (`bill_no`,`bill_row`),\n" +
                        "  KEY `inventory_region_record_bill_no_idx` (`bill_no`) USING BTREE,\n" +
                        "  KEY `lock_time` (`lock_time`) USING BTREE,\n" +
                        "  KEY `inventory_region_record_record_status_idx` (`record_status`) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='大区库存锁定和预留记录';\n";
                System.out.println(sql);
            }
        }

    }

    private void updateOnwayRecordChannel(Map<String, List<String>> channelBillMap) {
        String DB_FORMAT = "00";
        String TABLE_FORMAT = "000";
        DecimalFormat df = new DecimalFormat(DB_FORMAT);
        DecimalFormat tf = new DecimalFormat(TABLE_FORMAT);
        for (Map.Entry<String, List<String>> entry : channelBillMap.entrySet()) {
            String channelCode = entry.getKey();
            List<String> billNos = entry.getValue();
            for (int i = 1; i <= 6; i++) {
                for (int j = 1; j <= 128; j++) {
                    for (String billNo : billNos) {
                        String sql = "update snt_ims_sub_" + df.format(i) + ".inventory_onway_record_" + tf.format(j) + " set channel_code = '" + channelCode + "' where bill_code = '" + billNo + "' and channel_code != 'OTHER';";
                        System.out.println(sql);
                    }
                }
            }

        }

    }

}
